/*
 */

package Dblayer;

import Modellayer.*;
import java.util.ArrayList;
import java.sql.*;

/**
 *
 * @author bruhn
 */
public class DBBestilling implements IFDBBestilling {

    private Connection con;
    public DBBestilling()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    public ArrayList<Bestilling> hentBestilling(boolean retriveAssociation) {
          return miscWhere("", retriveAssociation);
    }

    public Bestilling findBestillingsNr(int bestillingsNr, boolean retriveAssociation) {
        String wClause = "  BestillingsNr = '" + bestillingsNr + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    public int opretBestilling(Bestilling bestilling) {
    int rc = -1;
    String query="INSERT INTO semBestilling(bestillingsNr, kommentar, pris, bstatus, medNr)  VALUES('"+
	  		bestilling.getBestillingsNr()  + "','"  +
			bestilling.getKommentar() + "','" +
			bestilling.getPris() + "','" +
			bestilling.isStatus() + "','" +
			bestilling.getMedNr().getMedNr() + "')";

      System.out.println("insert : " + query);
      try{ // indsæt ny bestilling
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//slut "try"
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in semBestilling db: "+ex);
	   }
	   return(rc);
	}

	public void slet(int bestillingsNr)
	{
               int rc=-1;

	  	String query="DELETE FROM semBestilling WHERE bestillingsNr = '" +
				bestillingsNr + "'";
                System.out.println(query);
	  	try{ // slet fra semBestilling
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut "try"
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in semBestilling db: "+ex);
   	        }
    }

    public int redigerBestilling(Bestilling bestilling) {
         Bestilling bestilObj  = bestilling;
		int rc=-1;

		String query="UPDATE semBestilling SET "+
                        "bestillingsNr ='" + bestilObj.getBestillingsNr() + "', " +
		    	"kommentar ='" + bestilObj.getKommentar() + "', " +
                        "bstatus ='" + bestilObj.isStatus() + "', " +
                        "medNr ='" + bestilObj.getMedNr() + "', " +
                        "pris ='" + bestilObj.getPris() + "' " +
		        " WHERE bestillingsNr = '"+ bestilObj.getBestillingsNr() + "'";

                System.out.println("Update query:" + query);
  		try{ // update vare
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut "try"
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in bestilling db: "+ex);
	  	}
		return(rc);
    }

    public void sletBestilling(int bestillingsNr) {
        int rc=-1;

	  	String query="DELETE FROM semBestilling WHERE bestillingsNr = '" +
				bestillingsNr + "'";
                System.out.println(query);
	  	try{ //slet fra semBestilling
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut "try"
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in semBestilling db: "+ex);
   	        }
    }
      private ArrayList<Bestilling> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Bestilling> list = new ArrayList<Bestilling>();

	    String query =  buildQuery(wClause);

            try{ //læs semBestilling fra databasen
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		int snr=0;
		while( results.next() ){
	     	 Bestilling bestilObj = new Bestilling();
		 bestilObj = buildBestilling(results);
                 list.add(bestilObj);
		}//slut while
                 stmt.close();

		}//slut "try"
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

    //Singelwhere er når der kun skal findes 1
    private Bestilling singleWhere(String wClause, boolean retrieveAssociation)
    {
           ResultSet results;
           Bestilling bestilObj = new Bestilling();

	   String query =  buildQuery(wClause);
           System.out.println(query);
		try{ //læs semBestilling fra databasen
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            bestilObj = buildBestilling(results);
                            //assosiationen bliver lavet hvis der var nogen
                            stmt.close();
			}
                        else{ //hvis ingen bestilling var fundet
                            bestilObj = null;
                        }
		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return bestilObj;
	}
	//metoden der bygger "query"
	private String buildQuery(String wClause)
	{
	    String query="SELECT bestillingsNr, kommentar, pris, bstatus, medNr FROM semBestilling";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Bestilling buildBestilling(ResultSet results)
        {
           Bestilling bestilObj = new Bestilling();
           Medarbejder medarbejderObj = new Medarbejder();
            try{ //værdierne fra semBestilling bliver brugt
                bestilObj.setBestillingsNr(results.getInt("bestillingsNr"));
                bestilObj.setKommentar(results.getString("kommentar"));
                bestilObj.setPris(results.getDouble("pris"));
                medarbejderObj.setMedNr(results.getInt("medNr"));
                bestilObj.setMedNr(medarbejderObj);
		bestilObj.setStatus((results.getBoolean("bstatus")));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Bestilling object");
         }
         return bestilObj;
      }
}
